package com.atguigu.realtime.app.dws;

import com.atguigu.realtime.app.BaseSQLApp;
import com.atguigu.realtime.bean.KeywordBean;
import com.atguigu.realtime.common.Constant;
import com.atguigu.realtime.function.IKAnalyzer;
import com.atguigu.realtime.util.FlinkSinkUtil;
import com.atguigu.realtime.util.SqlUtil;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.table.api.Table;
import org.apache.flink.table.api.bridge.java.StreamTableEnvironment;

/**
 * @Author lzc
 * @Date 2022/5/27 9:27
 */
public class Dws_01_DwsTrafficSourceKeywordPageViewWindow extends BaseSQLApp {
    public static void main(String[] args) {
        new Dws_01_DwsTrafficSourceKeywordPageViewWindow().init(3001, 2, "Dws_01_DwsTrafficSourceKeywordPageViewWindow");
    }
    
    @Override
    protected void handle(StreamExecutionEnvironment env,
                          StreamTableEnvironment tEnv) {
        // 1. 建立动态表读取 dwd 页面日志
        tEnv
            .executeSql("create table page_log(" +
                            " common map<string, string>, " +
                            " page map<string, string>, " +
                            " ts bigint, " +
                            " et as to_timestamp_ltz(ts, 3), " +
                            " watermark for et as et - interval '3' second " +
                            ")" + SqlUtil.getKafkaReadDDL(Constant.TOPIC_DWD_TRAFFIC_PAGE, "Dws_01_DwsTrafficSourceKeywordPageViewWindow"));
        
        // 2. 过滤其中的数据: 找到搜索记录
        // item is not null
        // item_type ='keyword'
        // "last_page_id": "search"
        Table searchTable = tEnv.sqlQuery("select " +
                                              " page['item'] keyword, " +
                                              " et " +
                                              "from page_log " +
                                              "where page['last_page_id']='search' " +
                                              "and page['item_type']='keyword' " +
                                              "and page['item'] is not null ");
        tEnv.createTemporaryView("search_table", searchTable);
        // 3. 对关键词进行分词  用到自定义TableFunction
        tEnv.createTemporaryFunction("ik_analyzer", IKAnalyzer.class);
        Table splitedTable = tEnv.sqlQuery("select" +
                                               " word, " +
                                               " et " +
                                               "from search_table " +
                                               "join lateral table(ik_analyzer(keyword)) on true");
        
        
        tEnv.createTemporaryView("split_table", splitedTable);
        // 4. 统计分词后的次每个的个数
        // 分组窗口(滚动 滑动 会话)   over
        // tvf (滚动 滑动 累积)
        Table resultTable = tEnv.sqlQuery("select " +
                                              " date_format(window_start, 'yyyy-MM-dd HH:mm:ss') stt, " +
                                              " date_format(window_end, 'yyyy-MM-dd HH:mm:ss') edt, " +
                                              " 'search' source, " +
                                              " word keyword, " +
                                              " count(*) keyword_count, " +
                                              " unix_timestamp() * 1000 ts " +
                                              "from table( tumble( table split_table, descriptor(et), interval '10' second) ) " +
                                              "group by window_start, window_end, word ");
        
        // 5. 写出到clickhouse中: 自定义sink
        tEnv
            .toAppendStream(resultTable, KeywordBean.class)
            .addSink(FlinkSinkUtil.getClickHouseSink(Constant.CLICKHOUSE_DB, "dws_traffic_source_keyword_page_view_window", KeywordBean.class));
    
        try {
            env.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
/*
clickhouse 数据库  jdbc来写

jdbc sink?


 */